﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FinanSoft.Entities;
using FinanSoft.Utilities;
using FinanSoft.BusinessLayer;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;

namespace FinanSoft.DataLayer
{
    public class AuxAccountDataManager
    {

        #region Singleton

        private AuxAccountDataManager()
        { 
        }

        /// <summary>
        /// Única instancia de esta clase
        /// </summary>
        private static AuxAccountDataManager _instance;

        /// <summary>
        /// Método para implementar el patrón de Singleton
        /// </summary>
        /// <returns>Instancia única de la clase</returns>
        public static AuxAccountDataManager GetInstance()
        {
            if (_instance == null)
                _instance = new AuxAccountDataManager();
            return _instance;
        }

        #endregion

        #region Métodos públicos de la clase
        /// <summary>
        /// Método que se encarga de crear una nueva cuenta de cliente y proveedor
        /// </summary>
        /// <param name="account">Recibe la cuenta que va a guardar como nueva</param>
        /// <returns>Retorna true si se logró con éxito, false si no</returns>
        public int CreateNewAuxiliarAccount(AuxiliarAccount account)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_CREATE_AUXILIAR_ACCOUNT;

                SqlParameter[] parameters = new SqlParameter[9];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_NAME, account.Name);
                parameters[1] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_CODE, account.AccountCode);
                parameters[2] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_OWNER_TYPE, (int)account.EntryType);
                parameters[3] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_CURRENCY, account.AccountCurrency.Id);
                parameters[4] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_CREDIT_LIMIT, account.LimitedCreditAmount);
                parameters[5] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_FATHER_ACCOUNT, account.Parent);
                parameters[6] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_OWNER, account.OwnerName);
                parameters[7] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_PAY_COND, account.PayCondition);
                parameters[8] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_TYPE, (int)account.AccountType);

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);

                if (result.HasRows)
                {
                    result.Read();
                    int idAccount = Convert.ToInt32(result[Constants.TAG_NAME_AUXILIAR_ACCOUNT_ID]);
                    return idAccount;
                }
                return -1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// Método que se encarga de borrar cuenta de cliente o proveedor
        /// </summary>
        /// <param name="idAccount">Recibe el id de la cuenta que va a eliminar</param>
        /// <returns>Retorna true si se logró con éxito, false si no</returns>
        public bool DeleteAuxiliarAccount(int idAccount)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_DELETE_AUXILIAR_ACCOUNT;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_ID, idAccount);

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Método que se encarga de modifica una cuenta de cliente o proveedor existente
        /// </summary>
        /// <param name="account">Recibe la cuenta que va a guardar</param>
        /// <returns>Retorna true si se logró con éxito, false si no</returns>
        public bool ModifyAuxiliarAccount(AuxiliarAccount account)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_EDIT_AUXILIAR_ACCOUNT;

                SqlParameter[] parameters = new SqlParameter[7];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_ID, account.AccountID);
                parameters[1] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_NAME, account.Name);
                parameters[2] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_CURRENCY, account.AccountCurrency.Id);
                parameters[3] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_CREDIT_LIMIT, account.LimitedCreditAmount);
                parameters[4] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_FATHER_ACCOUNT, account.Parent);
                parameters[5] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_OWNER, account.OwnerName);
                parameters[6] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_PAY_COND, account.PayCondition);

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Método que llama al SP que se encarga de crear una nueva condición de pago
        /// </summary>
        /// <param name="condName">Nombre de la condición de pago</param>
        /// <param name="days">Número de días hábiles</param>
        /// <returns>Si tuvo éxito o no</returns>
        public bool CreatePayCondition(String condName, int days)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_CREATE_PAY_COND;
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_PAY_COND_NAME, condName);
                parameters[1] = new SqlParameter(Constants.SP_PARAM_PAY_COND_DAYS, days);

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Método que se encarga de eliminar una condición de pago
        /// </summary>
        /// <param name="condName">Nombre de la condición de pago (es único)</param>
        /// <returns>Retorna si tuvo éxito o no</returns>
        public bool DeletePayCondition(String condName)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_DELETE_PAY_COND;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_PAY_COND_NAME, condName);

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                if (result.RecordsAffected != -1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Método que se encarga de consultar la lista de clientes y proveedores
        /// el SP devuelve un xml que hay que parsear para crear el Hashtable, donde
        /// el key es el id de la cuenta y el objeto es de tipo AuxiliarAcount
        /// </summary>
        /// <returns></returns>
        public Hashtable GetAuxiliarAccountsList()
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_AUXILIAR_ACCOUNTS;
                SqlParameter[] parameters = new SqlParameter[0];
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                if (result.HasRows)
                {
                    StringBuilder sb = new StringBuilder();
                    while(result.Read())
                        sb.Append(result.GetString(0));
                    dataHelper.CloseReader();
                    // Se crea un documento XML con el string recibido
                    XmlDocument xmlAccounts = new XmlDocument();
                    xmlAccounts.LoadXml(sb.ToString());
                    XmlNodeList auxAccNodes = xmlAccounts.GetElementsByTagName(Constants.AUXILIAR_ACCOUNT_NODE_NAME);
                    Hashtable hashResult = new Hashtable();
                    foreach (XmlNode auxAccNode in auxAccNodes)
                    {
                        AuxiliarAccount auxAccount = new AuxiliarAccount();
                        XmlNode detailsNode = auxAccNode[Constants.AUXILIAR_DETAILS_NODE_NAME];
                        XmlNode currencyNode = auxAccNode[Constants.AUXILIAR_CURRENCY_NODE_NAME];
                        auxAccount.AccountCode = detailsNode[Constants.AUXILIAR_CODE_NODE_NAME].InnerText;
                        auxAccount.AccountCurrency = MoneyManager.GetInstance().GetCurrency(Convert.ToInt32(currencyNode[Constants.AUXILIAR_CURRENCY_ID_NODE_NAME].InnerText));
                        auxAccount.AccountID = Convert.ToInt32(detailsNode[Constants.AUXILIAR_ID_NODE_NAME].InnerText);
                        auxAccount.AccountType = detailsNode[Constants.AUXILIAR_TYPE_NODE_NAME].InnerText.Equals(Constants.CLIENT_AUX_ACCOUNT) ? Constants.AuxiliarAccountType.client : Constants.AuxiliarAccountType.provider;
                        auxAccount.Amount = Convert.ToDouble(detailsNode[Constants.AUXILIAR_AMMOUNT_NODE_NAME].InnerText.Replace('.', ','));
                        auxAccount.EntryType = detailsNode[Constants.AUXILIAR_OWNER_TYPE_NODE_NAME].InnerText.Equals(Constants.PERSON_TYPE_AUX_ACCOUNT) ? Constants.AuxiliarEntryType.person : Constants.AuxiliarEntryType.company;
                        auxAccount.LimitedCreditAmount = Convert.ToDouble(detailsNode[Constants.AUXILIAR_CREDIT_LIMIT_NODE_NAME].InnerText);
                        auxAccount.Name = detailsNode[Constants.AUXILIAR_NAME_NODE_NAME].InnerText;
                        auxAccount.OwnerName = detailsNode[Constants.AUXILIAR_OWNER_NODE_NAME].InnerText;
                        auxAccount.Parent = Convert.ToInt32(detailsNode[Constants.AUXILIAR_FATHER_NODE_NAME].InnerText);
                        auxAccount.PayCondition = detailsNode[Constants.AUXILIAR_PAY_NODE_NAME].InnerText;
                        auxAccount.AlDebe = Convert.ToBoolean(Convert.ToInt32(detailsNode[Constants.AUXILIAR_AL_DEBE_NODE_NAME].InnerText));
                        hashResult.Add(auxAccount.AccountID, auxAccount);
                    }
                    return hashResult;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Método que se encarga de consultar la lista de clientes y proveedores
        /// segun el tipo que se solicite
        /// </summary>
        /// <returns> retorna una lista con las cuentas auxiliares segun el tipo solicitado</returns>
        public AuxiliarAccount[] GetAuxiliarAccountsListByType(int idTipoCtaAux)
        {
            AuxiliarAccount[] arrayAuxAccount;

            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_AUXILIAR_ACCOUNTS_BY_TYPE;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_AUXILIAR_TYPE, idTipoCtaAux);
                Console.WriteLine(parameters[0]);
                Console.WriteLine(Constants.SP_PARAM_AUXILIAR_TYPE + " *** " + idTipoCtaAux);
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                if (result.HasRows)
                {
                    result.Read();
                    string xmlString = result.GetString(0);
                    dataHelper.CloseReader();
                    
                    // Se crea un documento XML con el string recibido
                    XmlDocument xmlAccounts = new XmlDocument();
                    xmlAccounts.LoadXml(xmlString);
                    XmlNodeList auxAccNodes = xmlAccounts.GetElementsByTagName(Constants.AUXILIAR_ACCOUNT_NODE_NAME);

                    arrayAuxAccount = new AuxiliarAccount[auxAccNodes.Count];

                    // Por cada nodo principal
                    int periodIndex = 0;                                        

                    foreach (XmlNode auxAccNode in auxAccNodes)
                    {

                        AuxiliarAccount AuxAccount = new AuxiliarAccount();

                        XmlNode detailsNode = auxAccNode[Constants.AUXILIAR_DETAILS_NODE_NAME];
                        XmlNode currencyNode = auxAccNode[Constants.AUXILIAR_CURRENCY_NODE_NAME];

                        AuxAccount.AccountCode = detailsNode[Constants.AUXILIAR_CODE_NODE_NAME].InnerText;
                        AuxAccount.AccountCurrency = MoneyManager.GetInstance().GetCurrency(Convert.ToInt32(currencyNode[Constants.AUXILIAR_CURRENCY_ID_NODE_NAME].InnerText));
                        AuxAccount.AccountID = Convert.ToInt32(detailsNode[Constants.AUXILIAR_ID_NODE_NAME].InnerText);
                        AuxAccount.AccountType = detailsNode[Constants.AUXILIAR_TYPE_NODE_NAME].InnerText.Equals(Constants.CLIENT_AUX_ACCOUNT) ? Constants.AuxiliarAccountType.client : Constants.AuxiliarAccountType.provider;
                        AuxAccount.Amount = Convert.ToDouble(detailsNode[Constants.AUXILIAR_AMMOUNT_NODE_NAME].InnerText);
                        AuxAccount.EntryType = detailsNode[Constants.AUXILIAR_OWNER_TYPE_NODE_NAME].InnerText.Equals(Constants.PERSON_TYPE_AUX_ACCOUNT) ? Constants.AuxiliarEntryType.person : Constants.AuxiliarEntryType.company;
                        AuxAccount.LimitedCreditAmount = Convert.ToDouble(detailsNode[Constants.AUXILIAR_CREDIT_LIMIT_NODE_NAME].InnerText);
                        AuxAccount.Name = detailsNode[Constants.AUXILIAR_NAME_NODE_NAME].InnerText;
                        AuxAccount.OwnerName = detailsNode[Constants.AUXILIAR_OWNER_NODE_NAME].InnerText;
                        AuxAccount.Parent = Convert.ToInt32(detailsNode[Constants.AUXILIAR_FATHER_NODE_NAME].InnerText);
                        AuxAccount.PayCondition = detailsNode[Constants.AUXILIAR_PAY_NODE_NAME].InnerText;
                                                
                        arrayAuxAccount[periodIndex] = AuxAccount;
                        periodIndex++;

                    }

                    return arrayAuxAccount;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Método que se encarga de devolver las condiciones de pago disponibles
        /// </summary>
        /// <returns></returns>
        public List<String> GetPayConditionsList()
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_PAY_COND;
                SqlParameter[] parameters = new SqlParameter[0];
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                List<String> conditions = new List<String>();
                if (result.HasRows)
                {
                    while (result.Read())
                    {
                        String condition = (String)result[0];
                        conditions.Add(condition);
                    }
                }
                return conditions;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

    }
}
